package com.n.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.n.common.BaseDao;
import com.n.model.CustomerBillRecord;
import com.n.vo.YMBillRec;
@Repository("customerBillRecordDao")
public class CustomerBillRecordDao extends BaseDao<CustomerBillRecord> {
	static String TABLE_NAME = "customerbillrecord";
	public void del(int id){
		super.del(TABLE_NAME, id);
	}
	
	/**
	 * 加载客户账单
	 * @param customerId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<CustomerBillRecord> loadByCustomerId(int customerId){
		String hql = "from CustomerBillRecord where customerId = "+customerId;
		return ht.find(hql);		
	}
	
	/**
	 * 加载客户月账单
	 * @param customerId
	 * @param year
	 * @param month
	 * @return
	 */
	public List<CustomerBillRecord> loadCustomerYMBill(int customerId,int year,int month){
		String sql = "select * from customerbillrecord where c_customerId = "+customerId 
				+ " and YEAR(c_date)="+year
				+ " and MONTH(c_date)="+month
				+ " order by c_date asc";
		return jt.query(sql, new RecordRowMapper());		
	}
	
	/**
	 * 统计客户累计欠款&还款
	 * @param customerId
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map<Integer,Double> countAll(int customerId){
		String sql = "select c_type,sum(c_money) as t_money from customerbillrecord where c_customerId = "+customerId+
				" group by c_type";
		final Map<Integer,Double> map = new HashMap<Integer, Double>();
		jt.query(sql, new RowMapper(){
			@Override
			public Map<Integer,Double> mapRow(ResultSet r, int arg1) throws SQLException {
				map.put(r.getInt("c_type"), r.getDouble("t_money"));				
				return map;
			}
			
		});
		return map;
	}
	
	
	
	/**
	 * 按月份统计
	 * @param year
	 */
	public List<YMBillRec> countList(int year){
		String sql = "select r.c_customerId,c.c_name as c_customerName,"
				+"sum( case  when  month(c_date )= 1  and r.c_type=1 then c_money else 0 end )  as 'q_1',"
				+"sum( case  when month(c_date )= 2  and r.c_type=1 then c_money else 0 end )  as 'q_2',"
				+"sum( case  when month(c_date )= 3  and r.c_type=1 then c_money else 0 end )  as 'q_3',"
				+"sum( case  when month(c_date )=4  and r.c_type=1 then c_money else 0 end )  as 'q_4',"
				+"sum( case  when month(c_date )= 5 and r.c_type=1 then c_money else 0 end )  as 'q_5',"
				+"sum( case  when month(c_date )= 6 and r.c_type=1 then c_money else 0 end )  as 'q_6',"
				+"sum( case  when month(c_date )= 7 and r.c_type=1 then c_money else 0 end )  as 'q_7',"
				+"sum( case  when month(c_date )= 8 and r.c_type=1 then c_money else 0 end )  as 'q_8',"
				+"sum( case  when month(c_date )= 9 and r.c_type=1 then c_money else 0 end )  as 'q_9',"
				+"sum( case  when month(c_date )= 10 and r.c_type=1 then c_money else 0 end )  as 'q_10',"
				+"sum( case  when month(c_date )= 11 and r.c_type=1 then c_money else 0 end )  as 'q_11',"
				+"sum( case  when month(c_date )= 12 and r.c_type=1 then c_money else 0 end )  as 'q_12',"
				+"sum( case  when  month(c_date )= 1  and r.c_type=2 then c_money else 0 end )  as 'h_1',"
				+"sum( case  when month(c_date )= 2  and r.c_type=2 then c_money else 0 end )  as 'h_2',"
				+"sum( case  when month(c_date )= 3  and r.c_type=2 then c_money else 0 end )  as 'h_3',"
				+"sum( case  when month(c_date )=4  and r.c_type=2 then c_money else 0 end )  as 'h_4',"
				+"sum( case  when month(c_date )= 5 and r.c_type=2 then c_money else 0 end )  as 'h_5',"
				+"sum( case  when month(c_date )= 6 and r.c_type=2 then c_money else 0 end )  as 'h_6',"
				+"sum( case  when month(c_date )= 7 and r.c_type=2 then c_money else 0 end )  as 'h_7',"
				+"sum( case  when month(c_date )= 8 and r.c_type=2 then c_money else 0 end )  as 'h_8',"
				+"sum( case  when month(c_date )= 9 and r.c_type=2 then c_money else 0 end )  as 'h_9',"
				+"sum( case  when month(c_date )= 10 and r.c_type=2 then c_money else 0 end )  as 'h_10',"
				+"sum( case  when month(c_date )= 11 and r.c_type=2 then c_money else 0 end )  as 'h_11',"
				+"sum( case  when month(c_date )= 12 and r.c_type=2 then c_money else 0 end )  as 'h_12',"
				+"sum( case  when r.c_type=2 then c_money else 0 end )  as 'h_y',"
				+"sum( case  when r.c_type=1 then c_money else 0 end )  as 'q_y'"
				+" from customer c,customerbillrecord r where c.c_id=r.c_customerId "
				+" and YEAR(c_date)="+year
				+" group by r.c_customerId,c_customerName"
				+" order by q_y desc ";
		System.out.println(sql);
		
		return jt.query(sql, new YMBillRecordRowMapper());
	}
	
	
	
	
	
	class RecordRowMapper implements RowMapper<CustomerBillRecord>{

        @Override
        public CustomerBillRecord mapRow(ResultSet r, int arg1) throws SQLException {
        	CustomerBillRecord rec = new CustomerBillRecord();
            rec.setId(r.getLong("c_id"));
            rec.setDate(r.getDate("c_date"));
            rec.setType(r.getInt("c_type"));
            rec.setPayType(r.getInt("c_payType"));
            rec.setMoney(r.getDouble("c_money"));
            rec.setOptDate(r.getDate("c_optDate"));
            rec.setRemark(r.getString("c_remark"));
            rec.setOptUser(r.getString("c_optUser"));
            return rec;
        }
	    
	}
	
	class YMBillRecordRowMapper implements RowMapper<YMBillRec>{

        @Override
        public YMBillRec mapRow(ResultSet r, int arg1) throws SQLException {
        	YMBillRec rec= new YMBillRec();
            rec.setCustomerId(r.getInt("c_customerId"));
            rec.setCustomerName(r.getString("c_customerName"));
            
            rec.setQ_1(r.getDouble("q_1"));
            rec.setQ_2(r.getDouble("q_2"));
            rec.setQ_3(r.getDouble("q_3"));
            rec.setQ_4(r.getDouble("q_4"));
            rec.setQ_5(r.getDouble("q_5"));
            rec.setQ_6(r.getDouble("q_6"));
            rec.setQ_7(r.getDouble("q_7"));
            rec.setQ_8(r.getDouble("q_8"));
            rec.setQ_9(r.getDouble("q_9"));
            rec.setQ_10(r.getDouble("q_10"));
            rec.setQ_11(r.getDouble("q_11"));
            rec.setQ_12(r.getDouble("q_12"));
            
            rec.setH_1(r.getDouble("h_1"));
            rec.setH_2(r.getDouble("h_2"));
            rec.setH_3(r.getDouble("h_3"));
            rec.setH_4(r.getDouble("h_4"));
            rec.setH_5(r.getDouble("h_5"));
            rec.setH_6(r.getDouble("h_6"));
            rec.setH_7(r.getDouble("h_7"));
            rec.setH_8(r.getDouble("h_8"));
            rec.setH_9(r.getDouble("h_9"));
            rec.setH_10(r.getDouble("h_10"));
            rec.setH_11(r.getDouble("h_11"));
            rec.setH_12(r.getDouble("h_12"));
            
            rec.setQ_y(r.getDouble("q_y"));
            rec.setH_y(r.getDouble("h_y"));
            
            return rec;
        }
	    
	}
}
